﻿using Common.Contract.Authentication.Record;
using Common.Contract.SystemConfig.Record;
using CrossSelling.Core.Data;
using CrossSelling.Model.Interface;
using CrossSelling.Model.Model;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Data.Entity;

namespace CrossSelling.Provider
{
    public class CrossSellingProductProvider : GenericProvider, ICrossSellingProductProvider
    {
        public List<CrossSellingProduct> RetrieveListProductCrossSelling()
        {
            var data = repository.DbContext as CrossSellingEntities;
            var query = from x in data.CrossSellingProducts
                        where x.ActiveStatus == 1
                        select x;
            return query.ToList();
        }
        public List<CrossSellingCustomer> RetrieveCrossSellingByCif(string cif)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var query = from x in data.CrossSellingCustomers
                        where x.Cif.Equals(cif)
                        select x;
            return query.ToList();
        }

        public List<CustUsedProductInPast> RetrieveCustUsedProductInPastByCif(string cif)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var query = from x in data.CustUsedProductInPasts
                        where x.Cif.Equals(cif)
                        select x;
            return query.ToList();
        }

        public List<ProductSMEByProductHolding> RetrieveListHoldProductCrossSelling()
        {
            var data = repository.DbContext as CrossSellingEntities;
            var query = from x in data.ProductSMEByProductHoldings
                        select x;
            return query.ToList();
        }
        public List<CrossSellingCustomer> RetrieveListCustomerXselling(out int totalRecord, int product, UserRecord dao, FilterRecord textSearch = null, string lang = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var query = from x in data.CrossSellingCustomers
                        where x.ProductId == product
                        select x;

            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                query = lang.Equals("vi") ? query.Where(x => x.IndustryName.Contains(textSearch.Industry)) : query.Where(x => x.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusOpenDate))
            {
                List<string> listFilter = textSearch.CusOpenDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.CusOpenDate.Value >= FromDateCompare && x.CusOpenDate.Value <= ToDateCompare);
            }
            double from = 1;
            double to = 10;
            if (!string.IsNullOrEmpty(textSearch.Lift))
            {
                List<string> listFilterLift = textSearch.CusOpenDate.Split('-').ToList();
                from = Convert.ToDouble(textSearch.Lift.Split('-')[0]);
                to = Convert.ToDouble(textSearch.Lift.Split('-')[1]);
                query = query.Where(x => x.LiftIndex >= from && x.LiftIndex <= to);
            }

            totalRecord = query.Count();

            query = query.OrderBy(x => new { x.Region, x.BranchId, x.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }

        public List<CustUsedProductInPast> RetrieveListCustomerHoldProduct(out int totalRecord, UserRecord dao, string product, FilterRecord textSearch = null, string lang = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var query = from x in data.CustUsedProductInPasts
                        where x.Product.ToUpper().Equals(product.ToUpper())
                        select x;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                query = lang.Equals("vi") ? query.Where(x => x.IndustryName.Contains(textSearch.Industry)) : query.Where(x => x.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusOpenDate))
            {
                List<string> listFilter = textSearch.CusOpenDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.CusOpenDate.Value >= FromDateCompare && x.CusOpenDate.Value <= ToDateCompare);
            }
            totalRecord = query.Count();

            query = query.OrderBy(x => new { x.Region, x.BranchId, x.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }

        public List<PreApprovedCreditCard87> RetrieveListCustPreApprovedCreditCard(out int totalRecord, UserRecord dao, FilterRecord textSearch = null, string lang = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var query = from x in data.PreApprovedCreditCard87s.Include(k => k.Branchs)
                        select x;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Branchs.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Customers.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Customers.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.Customers.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                query = lang.Equals("vi") ? query.Where(x => x.Customers.IndustryName.Contains(textSearch.Industry)) : query.Where(x => x.Customers.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusOpenDate))
            {
                List<string> listFilter = textSearch.CusOpenDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.Customers.CusOpenDate.Value >= FromDateCompare && x.Customers.CusOpenDate.Value <= ToDateCompare);
            }
            List<string> listBalance = new List<string>();
            double fromValue = 0, toValue = 0;
            if (!string.IsNullOrEmpty(textSearch.Balance))
            {
                listBalance = textSearch.Balance.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                query = query.Where(x => x.HanMucTheToiDaDuocCap > fromValue && x.HanMucTheToiDaDuocCap < toValue);
            }
            totalRecord = query.Count();

            query = query.OrderBy(x => new { x.Branchs.Region, x.Branchs.BranchNameSme, x.Customers.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<PreApprovedCreditCard21> RetrieveListCustPreApprovedCreditCardForLoan(out int totalRecord, UserRecord dao, int type, FilterRecord textSearch = null, string lang = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var query = from x in data.PreApprovedCreditCard21s.Include(k => k.Branchs).Include(j => j.Customers)
                        select x;

            switch (type)
            {
                case 1:
                    query = query.Where(x => x.CheckLhdn != "NO" && x.CheckKhChuaCoThe.Equals("YES") && x.CheckBctcThue.Equals("YES") && x.CheckConGioiHanChoVayKhongTsbd.Equals("YES") && x.CheckKhoanVayPheDuyet.Equals("YES") && x.CheckThoiGianPheDuyet.Equals("YES"));
                    break;
                case 2:
                    query = query.Where(x => x.CheckKhChuaCoThe.Equals("YES") && x.CheckLhdn != "NO" && x.CheckBctcThue.Equals("NO") && x.CheckKhoanVayPheDuyet.Equals("YES") && x.CheckThoiGianPheDuyet.Equals("YES"));
                    break;
                case 3:
                    query = query.Where(x => ((x.CheckBctcThue.Equals("YES") && x.CheckConGioiHanChoVayKhongTsbd.Equals("NO")) || (x.CheckBctcThue.Equals("NO") && x.HanMucTinChapTrenHeThong > 5e9)) || x.CheckKhChuaCoThe.Equals("NO") || x.CheckThoiGianPheDuyet.Equals("NO") || x.CheckKhoanVayPheDuyet.Equals("NO") || x.CheckLhdn.Equals("NO"));
                    break;
                default:
                    break;
            }
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Branchs.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Customers.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Customers.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.Customers.CusName.Contains(textSearch.CustName));
            } 
            if (!string.IsNullOrEmpty(textSearch.CheckLhdn))
            {
                query = query.Where(x => x.CheckLhdn.ToUpper().Equals(textSearch.CheckLhdn));
            }
            if (!string.IsNullOrEmpty(textSearch.CheckKhChuaCoThe))
            {
                query = query.Where(x => x.CheckKhChuaCoThe.ToUpper().Equals(textSearch.CheckKhChuaCoThe));
            }
            if (!string.IsNullOrEmpty(textSearch.CheckKhoanVayPheDuyet))
            {
                query = query.Where(x => x.CheckKhoanVayPheDuyet.ToUpper().Equals(textSearch.CheckKhoanVayPheDuyet));
            }
            if (!string.IsNullOrEmpty(textSearch.CheckThoiGianPheDuyet))
            {
                query = query.Where(x => x.CheckThoiGianPheDuyet.ToUpper().Equals(textSearch.CheckThoiGianPheDuyet));
            }
            if (!string.IsNullOrEmpty(textSearch.CheckConGioiHanChoVayKhongTsbd))
            {
                query = query.Where(x => x.CheckConGioiHanChoVayKhongTsbd.ToUpper().Equals(textSearch.CheckConGioiHanChoVayKhongTsbd));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                query = lang.Equals("vi") ? query.Where(x => x.Customers.IndustryName.Contains(textSearch.Industry)) : query.Where(x => x.Customers.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusOpenDate))
            {
                List<string> listFilter = textSearch.CusOpenDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.Customers.CusOpenDate.Value >= FromDateCompare && x.Customers.CusOpenDate.Value <= ToDateCompare);
            }
            //query = query.Where(x => x.Rn == 1);
            totalRecord = query.Count();

            query = query.OrderBy(x => new { x.Branchs.Region, x.Branchs.BranchNameSme, x.Customers.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<PreApprovedBilNew> RetrieveListCustPreApprovedBil(out int totalRecord, UserRecord dao, FilterRecord textSearch = null, string lang = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var last90Days = DateTime.Now.AddDays(-90);
            var query = from x in data.PreApprovedBilNews
                        where x.DateApprove.Value > last90Days
                        select x;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchNameSme.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                query = lang.Equals("vi") ? query.Where(x => x.IndustryName.Contains(textSearch.Industry)) : query.Where(x => x.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusOpenDate))
            {
                List<string> listFilter = textSearch.CusOpenDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.CusOpenDate.Value >= FromDateCompare && x.CusOpenDate.Value <= ToDateCompare);
            }
            List<string> listBalance = new List<string>();
            double fromValue = 0, toValue = 0;
            if (!string.IsNullOrEmpty(textSearch.Balance))
            {
                listBalance = textSearch.Balance.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                query = query.Where(x => x.SumLimit > fromValue && x.SumLimit < toValue);
            }
            if (!string.IsNullOrEmpty(textSearch.Balance1))
            {
                listBalance = textSearch.Balance1.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                query = query.Where(x => x.LimitBillDuocCap > fromValue && x.LimitBillDuocCap < toValue);
            }
            totalRecord = query.Count();

            query = query.OrderBy(x => new { x.Region, x.BranchNameSme, x.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }

        public List<PreApprovedWC> RetrieveListCustPreApprovedWC(out int totalRecord, UserRecord dao, FilterRecord textSearch = null, string lang = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var toDay = DateTime.Now;
            var query = from x in data.PreApprovedWCs
                        where x.ExpiryDate.Value > toDay
                        select x;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchNameSme.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                query = lang.Equals("vi") ? query.Where(x => x.IndustryName.Contains(textSearch.Industry)) : query.Where(x => x.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusOpenDate))
            {
                List<string> listFilter = textSearch.CusOpenDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.CusOpenDate.Value >= FromDateCompare && x.CusOpenDate.Value <= ToDateCompare);
            }
            List<string> listBalance = new List<string>();
            double fromValue = 0, toValue = 0;
            if (!string.IsNullOrEmpty(textSearch.Balance))
            {
                listBalance = textSearch.Balance.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                query = query.Where(x => x.TongGiatriHanmuc > fromValue && x.TongGiatriHanmuc < toValue);
            }
            totalRecord = query.Count();

            query = query.OrderBy(x => new { x.Region, x.BranchNameSme, x.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }

        public List<RateLimit> RetrieveListCustRateLimit(out int totalRecord, UserRecord dao, FilterRecord textSearch = null, string lang = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CrossSellingEntities;
            var toDay = DateTime.Now;
            var query = from x in data.RateLimits
                        where x.AvailAmtQd > 0.1
                        select x;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchNameSme.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                query = lang.Equals("vi") ? query.Where(x => x.IndustryName.Contains(textSearch.Industry)) : query.Where(x => x.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusOpenDate))
            {
                List<string> listFilter = textSearch.CusOpenDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.ApprovalDate.Value >= FromDateCompare && x.ApprovalDate.Value <= ToDateCompare);
            }
            List<string> listBalance = new List<string>();
            double fromValue = 0, toValue = 0;
            if (!string.IsNullOrEmpty(textSearch.Balance))
            {
                listBalance = textSearch.Balance.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                query = query.Where(x => x.OnlineLimitQd > fromValue && x.OnlineLimitQd < toValue);
            }
            if (!string.IsNullOrEmpty(textSearch.Balance1))
            {
                listBalance = textSearch.Balance1.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                query = query.Where(x => x.AvailAmtQd > fromValue && x.AvailAmtQd < toValue);
            }
            if (!string.IsNullOrEmpty(textSearch.Lift))
            {
                listBalance = textSearch.Lift.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)100;
                query = query.Where(x => (x.TotalOsQd / x.OnlineLimitQd) * -100 > fromValue && (x.TotalOsQd / x.OnlineLimitQd) * -100 < toValue);
            }
            totalRecord = query.Count();

            query = query.OrderBy(x => new { x.Region, x.BranchNameSme, x.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
    }
}
